SQL> CREATE TABLE t (x VARCHAR2(30));
 
Table created.
 
SQL> INSERT INTO t VALUES ('SKYBLUE/user1@abc.com');
SQL> INSERT INTO t VALUES ('user2@abc.com');
SQL> INSERT INTO t VALUES ('user3@abc.com');
SQL> INSERT INTO t VALUES ('SKYBLUE/user4@abc.com');
SQL> SELECT x
  2  FROM   t
  3  /
 
X
------------------------------
SKYBLUE/user1@abc.com
user2@abc.com
user3@abc.com
SKYBLUE/user4@abc.com
 
SQL> <font color=blue>UPDATE t</font>
  2  <font color=blue>SET    x = SUBSTR(t.x</font>
  3  <font color=blue>           ,      INSTR(t.x,'/') + 1)</font>
  4  <font color=blue>WHERE  INSTR(t.x,'/') > 0</font>
  5  /
 
2 rows updated.
 
SQL> SELECT x
  2  FROM   t
  3  /
 
X
------------------------------
user1@abc.com
user2@abc.com
user3@abc.com
user4@abc.com
 
SQL>

SELECT distinct REPLACE (file_name, '_bp%_sabah.data', '') FROM rev_load_tbl

select substr(BLPERIOD,1,6), <function> as "table_name", count(NUM_OF_ROWS) 
from rev_load_tbl;

SOURCE TABLE                             01         02         03         04         
------ ------------------------- ---------- ---------- ---------- ---------- 
COINS  BILLED_NONRECURR_CHARGES         654        230        215        285        
       BILLED_RECURR_CHARGES          49342      12870      13182      16020      
       BILLED_SERVICE_CHARGES         12275       6357       6615       7113       
       CUSTOMER_ACCT_CHARGES           2850        571        642        738        
       CUST_ACCT_PAY_ADJ                818        839       1103        932        
       CUST_ACCT_STATUS                4571       4567       4627       4677       

MAYPAC BILLED_NONRECURR_CHARGES           2          0          3         
       BILLED_RECURR_CHARGES           5477       5467       5481       5457       
       BILLED_SERVICE_CHARGES          1591       1587       1580       1587       
       CUSTOMER_ACCT_CHARGES           1172       1170       1162       1169       
       CUST_ACCT_PAY_ADJ               1085       1049       1068       1081       
       CUST_ACCT_STATUS                2065       2052       2052       2049       

TELEX  BILLED_NONRECURR_CHARGES           0          4          7          
       BILLED_RECURR_CHARGES           3426       3402       3365       3305       
       BILLED_SERVICE_CHARGES          1271       1321       1274       1234       
       CUSTOMER_ACCT_CHARGES           1219       1269       1222       1182       
       CUST_ACCT_PAY_ADJ                911        890        823        873        
       CUST_ACCT_STATUS                1828       1825       1777       1769   

column TABLE format a25
column SOURCE format a6
break on SOURCE skip 1
set linesize 200
set pagesize 36
SELECT distinct 
decode(substr(file_name,1,3), 'MAY', 'MAYPAC', 'CIN', 'COINS',  'TLX', 'TELEX', 'NONE') "SOURCE",
substr(file_name,5, (INSTR (file_name, '.200') -5)) "TABLE",
sum(decode (substr(file_name,-4,2), 01, num_of_rows, 000000000)) "01",
sum(decode (substr(file_name,-4,2), 02, num_of_rows, 000000000)) "02",
sum(decode (substr(file_name,-4,2), 03, num_of_rows, 000000000)) "03",
sum(decode (substr(file_name,-4,2), 04, num_of_rows, 000000000)) "04",
sum(decode (substr(file_name,-4,2), 05, num_of_rows, 000000000)) "05",
sum(decode (substr(file_name,-4,2), 06, num_of_rows, 000000000)) "06",
sum(decode (substr(file_name,-4,2), 07, num_of_rows, 000000000)) "07",
sum(decode (substr(file_name,-4,2), 08, num_of_rows, 000000000)) "08",
sum(decode (substr(file_name,-4,2), 09, num_of_rows, 000000000)) "09",
sum(decode (substr(file_name,-4,2), 10, num_of_rows, 000000000)) "10",
sum(decode (substr(file_name,-4,2), 11, num_of_rows, 000000000)) "11",
sum(decode (substr(file_name,-4,2), 12, num_of_rows, 000000000)) "12"
from rev_load_tbl 
where substr(file_name,1,3) in ('MAY', 'CIN', 'TLX') and substr(file_name,-8,4) = '2006'
group by substr(file_name,1,3), substr(file_name,5, (INSTR (file_name, '.200') -5));

SELECT DECODE (SUBSTR (FILE_NAME, 1, 3),
               'MAY', 'MAYPAC',
               'CIN', 'COINS',
               'TLX', 'TELEX',
               'NONE'
              ) "SOURCE",
       (CASE
           WHEN (   FILE_NAME LIKE '%ICAMS%'
                 OR FILE_NAME LIKE '%WAMS%')
              THEN SUBSTR (FILE_NAME, 1, (INSTR (FILE_NAME, '.200') - 1))
           ELSE SUBSTR (FILE_NAME, 5, (INSTR (FILE_NAME, '.200') - 5))
        END
       ) "Table"
FROM    REV_LOAD_TBL;

SELECT 
decode(file_name), 
like 'IB%', 'IBWAMS', 
like '%WAMS', 'WAMS',  
like '%ICAMS', 'ICAMS', 
'NONE') "SOURCE" 
from rev_load_tbl;

select distinct (CASE
           WHEN ( FILE_NAME LIKE 'IB%WAMS') THEN 'IBWAMS'
		   WHEN ( FILE_NAME LIKE '%ICAMS') THEN 'ICAMS'
           ELSE 'WAMS' 
		end) "SOURCE"
	FROM REV_LOAD_TBL
	where file_name like '%-%';

for both 'IBWAMS' and 'WAMS' --> substr(file_name,-7,2)
whereas for 'ICAMS' --> substr(file_name,-8,2)

column TABLE format a26
column SOURCE format a6
break on SOURCE skip 1
set linesize 200
set pagesize 36
select distinct 
(CASE
	WHEN ( FILE_NAME LIKE 'IB%WAMS') THEN 'IBWAMS'
	WHEN ( FILE_NAME LIKE '%ICAMS') THEN 'ICAMS'
	ELSE 'WAMS' 
end) as "SOURCE",
substr(file_name,1, (INSTR (file_name, '.200') -1)) "TABLE",
(CASE
	WHEN ( FILE_NAME LIKE '%WAMS') THEN sum(decode (substr(file_name,-7,2), '10', num_of_rows, 000000000))
	ELSE sum(decode (substr(file_name,-8,2), '10', num_of_rows, 000000000))
end)  "10"
FROM REV_LOAD_TBL where file_name like '%-%' and file_name like '%2006%'
group by file_name, substr(file_name,1, (INSTR (file_name, '.200') -1));

SQL> select case when col = 1 then 1 else 9 end  col
  2        ,sum(val)
  3  from (select 1 col, 2 val from dual union all
  4        select 1 col, 3 val from dual union all
  5        select 2 col, 1 val from dual union all
  6        select 3 col, 2 val from dual union all
  7        select 4 col, 3 val from dual)
  8  group by case when col = 1 then 1 else 9 end;

                           COL                       SUM(VAL)
------------------------------ ------------------------------
                             1                              5
                             9                              6